{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import glob as glob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['UK_full.xlsx',\n",
       " 'UK_source_post1946.xlsx',\n",
       " 'UK_source_pre1946.xlsx',\n",
       " 'US_full.xlsx',\n",
       " 'US_source_post1929.xlsx',\n",
       " 'US_source_pre1929.xlsx']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files=glob.glob('*.xlsx')\n",
    "files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>tau</th>\n",
       "      <th>g</th>\n",
       "      <th>s</th>\n",
       "      <th>x</th>\n",
       "      <th>pi</th>\n",
       "      <th>1yr CMT</th>\n",
       "      <th>10yr CMT</th>\n",
       "      <th>pdm</th>\n",
       "      <th>divgrm</th>\n",
       "      <th>debt_gdp_sargenthall</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1790</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.036786</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.277531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1791</td>\n",
       "      <td>0.021451</td>\n",
       "      <td>0.009318</td>\n",
       "      <td>0.012133</td>\n",
       "      <td>0.058252</td>\n",
       "      <td>0.026728</td>\n",
       "      <td>4.485066</td>\n",
       "      <td>4.528</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.349743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1792</td>\n",
       "      <td>0.016311</td>\n",
       "      <td>0.008346</td>\n",
       "      <td>0.007965</td>\n",
       "      <td>0.071138</td>\n",
       "      <td>0.018509</td>\n",
       "      <td>4.790878</td>\n",
       "      <td>5.217</td>\n",
       "      <td>2.654983</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.298771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1793</td>\n",
       "      <td>0.018538</td>\n",
       "      <td>0.006813</td>\n",
       "      <td>0.011725</td>\n",
       "      <td>0.076441</td>\n",
       "      <td>0.033938</td>\n",
       "      <td>5.896670</td>\n",
       "      <td>6.000</td>\n",
       "      <td>2.664991</td>\n",
       "      <td>-0.148525</td>\n",
       "      <td>0.250550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1794</td>\n",
       "      <td>0.017244</td>\n",
       "      <td>0.011113</td>\n",
       "      <td>0.006131</td>\n",
       "      <td>0.124177</td>\n",
       "      <td>0.103900</td>\n",
       "      <td>5.833949</td>\n",
       "      <td>5.455</td>\n",
       "      <td>2.791975</td>\n",
       "      <td>-0.011750</td>\n",
       "      <td>0.218043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>1925</td>\n",
       "      <td>0.041336</td>\n",
       "      <td>0.023853</td>\n",
       "      <td>0.017484</td>\n",
       "      <td>0.023219</td>\n",
       "      <td>0.034094</td>\n",
       "      <td>4.180000</td>\n",
       "      <td>3.800</td>\n",
       "      <td>3.030324</td>\n",
       "      <td>0.174962</td>\n",
       "      <td>0.211334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>1926</td>\n",
       "      <td>0.040484</td>\n",
       "      <td>0.023146</td>\n",
       "      <td>0.017338</td>\n",
       "      <td>0.063297</td>\n",
       "      <td>-0.011236</td>\n",
       "      <td>4.500000</td>\n",
       "      <td>3.560</td>\n",
       "      <td>2.958436</td>\n",
       "      <td>0.135296</td>\n",
       "      <td>0.196014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>1927</td>\n",
       "      <td>0.042807</td>\n",
       "      <td>0.022671</td>\n",
       "      <td>0.020135</td>\n",
       "      <td>0.009612</td>\n",
       "      <td>-0.022858</td>\n",
       "      <td>4.060000</td>\n",
       "      <td>3.170</td>\n",
       "      <td>3.158251</td>\n",
       "      <td>0.077123</td>\n",
       "      <td>0.191864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>1928</td>\n",
       "      <td>0.041120</td>\n",
       "      <td>0.024124</td>\n",
       "      <td>0.016997</td>\n",
       "      <td>0.011395</td>\n",
       "      <td>-0.011628</td>\n",
       "      <td>6.040000</td>\n",
       "      <td>3.450</td>\n",
       "      <td>3.358138</td>\n",
       "      <td>0.133977</td>\n",
       "      <td>0.162726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>1929</td>\n",
       "      <td>0.038575</td>\n",
       "      <td>0.025063</td>\n",
       "      <td>0.013512</td>\n",
       "      <td>0.059720</td>\n",
       "      <td>0.005831</td>\n",
       "      <td>7.610000</td>\n",
       "      <td>3.360</td>\n",
       "      <td>3.134994</td>\n",
       "      <td>0.091197</td>\n",
       "      <td>0.152476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>140 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     year       tau         g         s         x        pi   1yr CMT  \\\n",
       "0    1790       NaN       NaN       NaN       NaN  0.036786       NaN   \n",
       "1    1791  0.021451  0.009318  0.012133  0.058252  0.026728  4.485066   \n",
       "2    1792  0.016311  0.008346  0.007965  0.071138  0.018509  4.790878   \n",
       "3    1793  0.018538  0.006813  0.011725  0.076441  0.033938  5.896670   \n",
       "4    1794  0.017244  0.011113  0.006131  0.124177  0.103900  5.833949   \n",
       "..    ...       ...       ...       ...       ...       ...       ...   \n",
       "135  1925  0.041336  0.023853  0.017484  0.023219  0.034094  4.180000   \n",
       "136  1926  0.040484  0.023146  0.017338  0.063297 -0.011236  4.500000   \n",
       "137  1927  0.042807  0.022671  0.020135  0.009612 -0.022858  4.060000   \n",
       "138  1928  0.041120  0.024124  0.016997  0.011395 -0.011628  6.040000   \n",
       "139  1929  0.038575  0.025063  0.013512  0.059720  0.005831  7.610000   \n",
       "\n",
       "     10yr CMT       pdm    divgrm  debt_gdp_sargenthall  \n",
       "0       6.000       NaN       NaN              0.277531  \n",
       "1       4.528       NaN       NaN              0.349743  \n",
       "2       5.217  2.654983       NaN              0.298771  \n",
       "3       6.000  2.664991 -0.148525              0.250550  \n",
       "4       5.455  2.791975 -0.011750              0.218043  \n",
       "..        ...       ...       ...                   ...  \n",
       "135     3.800  3.030324  0.174962              0.211334  \n",
       "136     3.560  2.958436  0.135296              0.196014  \n",
       "137     3.170  3.158251  0.077123              0.191864  \n",
       "138     3.450  3.358138  0.133977              0.162726  \n",
       "139     3.360  3.134994  0.091197              0.152476  \n",
       "\n",
       "[140 rows x 11 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "US1=pd.read_excel('US_source_pre1929.xlsx',sheet_name='Final Data')\n",
    "US1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>tau</th>\n",
       "      <th>g</th>\n",
       "      <th>s</th>\n",
       "      <th>x</th>\n",
       "      <th>pi</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>3mo CMT</th>\n",
       "      <th>1yr CMT</th>\n",
       "      <th>2yr CMT</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 26</th>\n",
       "      <th>Unnamed: 27</th>\n",
       "      <th>Unnamed: 28</th>\n",
       "      <th>Unnamed: 29</th>\n",
       "      <th>Unnamed: 30</th>\n",
       "      <th>tips5</th>\n",
       "      <th>tips7</th>\n",
       "      <th>tips10</th>\n",
       "      <th>tips20</th>\n",
       "      <th>tips30</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1929</td>\n",
       "      <td>0.035373</td>\n",
       "      <td>0.024857</td>\n",
       "      <td>0.010516</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.030</td>\n",
       "      <td>3.030</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1930</td>\n",
       "      <td>0.031453</td>\n",
       "      <td>0.029284</td>\n",
       "      <td>0.002169</td>\n",
       "      <td>-0.086762</td>\n",
       "      <td>-0.039421</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.480</td>\n",
       "      <td>1.480</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1931</td>\n",
       "      <td>0.024548</td>\n",
       "      <td>0.050388</td>\n",
       "      <td>-0.025840</td>\n",
       "      <td>-0.070941</td>\n",
       "      <td>-0.104032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.410</td>\n",
       "      <td>2.410</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1932</td>\n",
       "      <td>0.026891</td>\n",
       "      <td>0.045378</td>\n",
       "      <td>-0.018487</td>\n",
       "      <td>-0.142042</td>\n",
       "      <td>-0.120968</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.085</td>\n",
       "      <td>0.085</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1933</td>\n",
       "      <td>0.045455</td>\n",
       "      <td>0.055944</td>\n",
       "      <td>-0.010490</td>\n",
       "      <td>-0.012000</td>\n",
       "      <td>-0.027422</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.695</td>\n",
       "      <td>0.695</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>2016</td>\n",
       "      <td>0.185278</td>\n",
       "      <td>0.198277</td>\n",
       "      <td>-0.012998</td>\n",
       "      <td>0.016613</td>\n",
       "      <td>0.009897</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.510</td>\n",
       "      <td>0.850</td>\n",
       "      <td>1.20</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.36</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.82</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2017</td>\n",
       "      <td>0.180969</td>\n",
       "      <td>0.195019</td>\n",
       "      <td>-0.014051</td>\n",
       "      <td>0.022000</td>\n",
       "      <td>0.019106</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.390</td>\n",
       "      <td>1.760</td>\n",
       "      <td>1.89</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.48</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.61</td>\n",
       "      <td>0.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>2018</td>\n",
       "      <td>0.173867</td>\n",
       "      <td>0.194459</td>\n",
       "      <td>-0.020592</td>\n",
       "      <td>0.028683</td>\n",
       "      <td>0.023700</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.450</td>\n",
       "      <td>2.630</td>\n",
       "      <td>2.48</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.04</td>\n",
       "      <td>0.98</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>2019</td>\n",
       "      <td>0.173760</td>\n",
       "      <td>0.197229</td>\n",
       "      <td>-0.023469</td>\n",
       "      <td>0.022812</td>\n",
       "      <td>0.017547</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.550</td>\n",
       "      <td>1.590</td>\n",
       "      <td>1.58</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>2020</td>\n",
       "      <td>0.176345</td>\n",
       "      <td>0.300751</td>\n",
       "      <td>-0.124406</td>\n",
       "      <td>-0.035590</td>\n",
       "      <td>0.012928</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.090</td>\n",
       "      <td>0.100</td>\n",
       "      <td>0.13</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.59</td>\n",
       "      <td>-1.24</td>\n",
       "      <td>-1.06</td>\n",
       "      <td>-0.61</td>\n",
       "      <td>-0.37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>92 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    year       tau         g         s         x        pi  Unnamed: 6  \\\n",
       "0   1929  0.035373  0.024857  0.010516       NaN       NaN         NaN   \n",
       "1   1930  0.031453  0.029284  0.002169 -0.086762 -0.039421         NaN   \n",
       "2   1931  0.024548  0.050388 -0.025840 -0.070941 -0.104032         NaN   \n",
       "3   1932  0.026891  0.045378 -0.018487 -0.142042 -0.120968         NaN   \n",
       "4   1933  0.045455  0.055944 -0.010490 -0.012000 -0.027422         NaN   \n",
       "..   ...       ...       ...       ...       ...       ...         ...   \n",
       "87  2016  0.185278  0.198277 -0.012998  0.016613  0.009897         NaN   \n",
       "88  2017  0.180969  0.195019 -0.014051  0.022000  0.019106         NaN   \n",
       "89  2018  0.173867  0.194459 -0.020592  0.028683  0.023700         NaN   \n",
       "90  2019  0.173760  0.197229 -0.023469  0.022812  0.017547         NaN   \n",
       "91  2020  0.176345  0.300751 -0.124406 -0.035590  0.012928         NaN   \n",
       "\n",
       "    3mo CMT  1yr CMT  2yr CMT  ...  Unnamed: 26  Unnamed: 27  Unnamed: 28  \\\n",
       "0     3.030    3.030      NaN  ...          NaN          NaN          NaN   \n",
       "1     1.480    1.480      NaN  ...          NaN          NaN          NaN   \n",
       "2     2.410    2.410      NaN  ...          NaN          NaN          NaN   \n",
       "3     0.085    0.085      NaN  ...          NaN          NaN          NaN   \n",
       "4     0.695    0.695      NaN  ...          NaN          NaN          NaN   \n",
       "..      ...      ...      ...  ...          ...          ...          ...   \n",
       "87    0.510    0.850     1.20  ...          NaN          NaN          NaN   \n",
       "88    1.390    1.760     1.89  ...          NaN          NaN          NaN   \n",
       "89    2.450    2.630     2.48  ...          NaN          NaN          NaN   \n",
       "90    1.550    1.590     1.58  ...          NaN          NaN          NaN   \n",
       "91    0.090    0.100     0.13  ...          NaN          NaN          NaN   \n",
       "\n",
       "    Unnamed: 29  Unnamed: 30  tips5  tips7  tips10  tips20  tips30  \n",
       "0           NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "1           NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "2           NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "3           NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "4           NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "..          ...          ...    ...    ...     ...     ...     ...  \n",
       "87          NaN          NaN   0.09   0.36    0.50    0.82    0.99  \n",
       "88          NaN          NaN   0.34   0.48    0.44    0.61    0.73  \n",
       "89          NaN          NaN   1.00   1.04    0.98    1.09    1.21  \n",
       "90          NaN          NaN   0.01   0.09    0.15    0.39    0.58  \n",
       "91          NaN          NaN  -1.59  -1.24   -1.06   -0.61   -0.37  \n",
       "\n",
       "[92 rows x 36 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "US2=pd.read_excel('US_source_post1929.xlsx',sheet_name='VAR2')\n",
    "US2\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>tau</th>\n",
       "      <th>g</th>\n",
       "      <th>s</th>\n",
       "      <th>x</th>\n",
       "      <th>pi</th>\n",
       "      <th>1yr CMT</th>\n",
       "      <th>10yr CMT</th>\n",
       "      <th>pdm</th>\n",
       "      <th>divgrm</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 26</th>\n",
       "      <th>Unnamed: 27</th>\n",
       "      <th>Unnamed: 28</th>\n",
       "      <th>Unnamed: 29</th>\n",
       "      <th>Unnamed: 30</th>\n",
       "      <th>tips5</th>\n",
       "      <th>tips7</th>\n",
       "      <th>tips10</th>\n",
       "      <th>tips20</th>\n",
       "      <th>tips30</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1790</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.036786</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1791</td>\n",
       "      <td>0.021451</td>\n",
       "      <td>0.009318</td>\n",
       "      <td>0.012133</td>\n",
       "      <td>0.058252</td>\n",
       "      <td>0.026728</td>\n",
       "      <td>4.485066</td>\n",
       "      <td>4.528</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1792</td>\n",
       "      <td>0.016311</td>\n",
       "      <td>0.008346</td>\n",
       "      <td>0.007965</td>\n",
       "      <td>0.071138</td>\n",
       "      <td>0.018509</td>\n",
       "      <td>4.790878</td>\n",
       "      <td>5.217</td>\n",
       "      <td>2.654983</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1793</td>\n",
       "      <td>0.018538</td>\n",
       "      <td>0.006813</td>\n",
       "      <td>0.011725</td>\n",
       "      <td>0.076441</td>\n",
       "      <td>0.033938</td>\n",
       "      <td>5.896670</td>\n",
       "      <td>6.000</td>\n",
       "      <td>2.664991</td>\n",
       "      <td>-0.148525</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1794</td>\n",
       "      <td>0.017244</td>\n",
       "      <td>0.011113</td>\n",
       "      <td>0.006131</td>\n",
       "      <td>0.124177</td>\n",
       "      <td>0.103900</td>\n",
       "      <td>5.833949</td>\n",
       "      <td>5.455</td>\n",
       "      <td>2.791975</td>\n",
       "      <td>-0.011750</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>1925</td>\n",
       "      <td>0.041336</td>\n",
       "      <td>0.023853</td>\n",
       "      <td>0.017484</td>\n",
       "      <td>0.023219</td>\n",
       "      <td>0.034094</td>\n",
       "      <td>4.180000</td>\n",
       "      <td>3.800</td>\n",
       "      <td>3.030324</td>\n",
       "      <td>0.174962</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>1926</td>\n",
       "      <td>0.040484</td>\n",
       "      <td>0.023146</td>\n",
       "      <td>0.017338</td>\n",
       "      <td>0.063297</td>\n",
       "      <td>-0.011236</td>\n",
       "      <td>4.500000</td>\n",
       "      <td>3.560</td>\n",
       "      <td>2.958436</td>\n",
       "      <td>0.135296</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>1927</td>\n",
       "      <td>0.042807</td>\n",
       "      <td>0.022671</td>\n",
       "      <td>0.020135</td>\n",
       "      <td>0.009612</td>\n",
       "      <td>-0.022858</td>\n",
       "      <td>4.060000</td>\n",
       "      <td>3.170</td>\n",
       "      <td>3.158251</td>\n",
       "      <td>0.077123</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>1928</td>\n",
       "      <td>0.041120</td>\n",
       "      <td>0.024124</td>\n",
       "      <td>0.016997</td>\n",
       "      <td>0.011395</td>\n",
       "      <td>-0.011628</td>\n",
       "      <td>6.040000</td>\n",
       "      <td>3.450</td>\n",
       "      <td>3.358138</td>\n",
       "      <td>0.133977</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>1929</td>\n",
       "      <td>0.038575</td>\n",
       "      <td>0.025063</td>\n",
       "      <td>0.013512</td>\n",
       "      <td>0.059720</td>\n",
       "      <td>0.005831</td>\n",
       "      <td>7.610000</td>\n",
       "      <td>3.360</td>\n",
       "      <td>3.134994</td>\n",
       "      <td>0.091197</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>140 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     year       tau         g         s         x        pi   1yr CMT  \\\n",
       "0    1790       NaN       NaN       NaN       NaN  0.036786       NaN   \n",
       "1    1791  0.021451  0.009318  0.012133  0.058252  0.026728  4.485066   \n",
       "2    1792  0.016311  0.008346  0.007965  0.071138  0.018509  4.790878   \n",
       "3    1793  0.018538  0.006813  0.011725  0.076441  0.033938  5.896670   \n",
       "4    1794  0.017244  0.011113  0.006131  0.124177  0.103900  5.833949   \n",
       "..    ...       ...       ...       ...       ...       ...       ...   \n",
       "135  1925  0.041336  0.023853  0.017484  0.023219  0.034094  4.180000   \n",
       "136  1926  0.040484  0.023146  0.017338  0.063297 -0.011236  4.500000   \n",
       "137  1927  0.042807  0.022671  0.020135  0.009612 -0.022858  4.060000   \n",
       "138  1928  0.041120  0.024124  0.016997  0.011395 -0.011628  6.040000   \n",
       "139  1929  0.038575  0.025063  0.013512  0.059720  0.005831  7.610000   \n",
       "\n",
       "     10yr CMT       pdm    divgrm  ...  Unnamed: 26  Unnamed: 27  Unnamed: 28  \\\n",
       "0       6.000       NaN       NaN  ...          NaN          NaN          NaN   \n",
       "1       4.528       NaN       NaN  ...          NaN          NaN          NaN   \n",
       "2       5.217  2.654983       NaN  ...          NaN          NaN          NaN   \n",
       "3       6.000  2.664991 -0.148525  ...          NaN          NaN          NaN   \n",
       "4       5.455  2.791975 -0.011750  ...          NaN          NaN          NaN   \n",
       "..        ...       ...       ...  ...          ...          ...          ...   \n",
       "135     3.800  3.030324  0.174962  ...          NaN          NaN          NaN   \n",
       "136     3.560  2.958436  0.135296  ...          NaN          NaN          NaN   \n",
       "137     3.170  3.158251  0.077123  ...          NaN          NaN          NaN   \n",
       "138     3.450  3.358138  0.133977  ...          NaN          NaN          NaN   \n",
       "139     3.360  3.134994  0.091197  ...          NaN          NaN          NaN   \n",
       "\n",
       "     Unnamed: 29  Unnamed: 30  tips5  tips7  tips10  tips20  tips30  \n",
       "0            NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "1            NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "2            NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "3            NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "4            NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "..           ...          ...    ...    ...     ...     ...     ...  \n",
       "135          NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "136          NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "137          NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "138          NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "139          NaN          NaN    NaN    NaN     NaN     NaN     NaN  \n",
       "\n",
       "[140 rows x 36 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i in US2.columns:\n",
    "    if i not in US1.columns:\n",
    "        US1[i]=np.nan\n",
    "US1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ADMINI~1\\AppData\\Local\\Temp/ipykernel_28940/696319460.py:6: UserWarning: Pandas requires version '3.0.3' or newer of 'xlsxwriter' (version '3.0.1' currently installed).\n",
      "  US_full.to_excel('US_full.xlsx',index=False)\n"
     ]
    }
   ],
   "source": [
    "US2.loc[(US2['year']==1929),'x']=US1.loc[(US1['year']==1929),'x'].values\n",
    "US2.loc[(US2['year']==1929),'pi']=US1.loc[(US1['year']==1929),'pi'].values\n",
    "\n",
    "US_full=pd.concat([US1.iloc[:-1],US2],axis=0)\n",
    "\n",
    "US_full.to_excel('US_full.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>inflation</th>\n",
       "      <th>real gdp growth</th>\n",
       "      <th>revtogdp</th>\n",
       "      <th>spendingtogdp</th>\n",
       "      <th>stockindex</th>\n",
       "      <th>rate_10year</th>\n",
       "      <th>short_r</th>\n",
       "      <th>debttogdplevel</th>\n",
       "      <th>dy</th>\n",
       "      <th>coin_bullion/debt</th>\n",
       "      <th>debttogdp-bullion</th>\n",
       "      <th>debttogdp_mil</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1700</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.051348</td>\n",
       "      <td>0.024282</td>\n",
       "      <td>-0.176614</td>\n",
       "      <td>6.349</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.064618</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1701</td>\n",
       "      <td>-0.049716</td>\n",
       "      <td>0.085921</td>\n",
       "      <td>0.047680</td>\n",
       "      <td>0.031056</td>\n",
       "      <td>-0.274218</td>\n",
       "      <td>8.264</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.060818</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1702</td>\n",
       "      <td>-0.001810</td>\n",
       "      <td>-0.026129</td>\n",
       "      <td>0.061131</td>\n",
       "      <td>0.047718</td>\n",
       "      <td>-0.212283</td>\n",
       "      <td>6.557</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.064613</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1703</td>\n",
       "      <td>-0.009189</td>\n",
       "      <td>-0.066233</td>\n",
       "      <td>0.072159</td>\n",
       "      <td>0.057088</td>\n",
       "      <td>-0.062131</td>\n",
       "      <td>5.714</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.061515</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1704</td>\n",
       "      <td>0.017989</td>\n",
       "      <td>0.174375</td>\n",
       "      <td>0.057906</td>\n",
       "      <td>0.049762</td>\n",
       "      <td>-0.062131</td>\n",
       "      <td>5.917</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.055999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>1942</td>\n",
       "      <td>0.073617</td>\n",
       "      <td>0.017772</td>\n",
       "      <td>0.294740</td>\n",
       "      <td>0.559769</td>\n",
       "      <td>3.570698</td>\n",
       "      <td>2.960</td>\n",
       "      <td>1.031</td>\n",
       "      <td>0.855553</td>\n",
       "      <td>0.044000</td>\n",
       "      <td>0.000074</td>\n",
       "      <td>0.855489</td>\n",
       "      <td>1.555954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>1943</td>\n",
       "      <td>0.045327</td>\n",
       "      <td>0.017036</td>\n",
       "      <td>0.313066</td>\n",
       "      <td>0.555163</td>\n",
       "      <td>3.633418</td>\n",
       "      <td>2.980</td>\n",
       "      <td>1.031</td>\n",
       "      <td>0.892668</td>\n",
       "      <td>0.041000</td>\n",
       "      <td>0.000125</td>\n",
       "      <td>0.892556</td>\n",
       "      <td>1.748170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>1944</td>\n",
       "      <td>0.053630</td>\n",
       "      <td>-0.045000</td>\n",
       "      <td>0.331536</td>\n",
       "      <td>0.570380</td>\n",
       "      <td>3.688273</td>\n",
       "      <td>2.980</td>\n",
       "      <td>1.031</td>\n",
       "      <td>0.980120</td>\n",
       "      <td>0.038000</td>\n",
       "      <td>0.000116</td>\n",
       "      <td>0.980007</td>\n",
       "      <td>1.995914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>1945</td>\n",
       "      <td>0.019649</td>\n",
       "      <td>-0.046810</td>\n",
       "      <td>0.349559</td>\n",
       "      <td>0.543722</td>\n",
       "      <td>3.733123</td>\n",
       "      <td>2.960</td>\n",
       "      <td>0.531</td>\n",
       "      <td>1.155510</td>\n",
       "      <td>0.038000</td>\n",
       "      <td>0.000171</td>\n",
       "      <td>1.155313</td>\n",
       "      <td>2.354168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>1946</td>\n",
       "      <td>0.031011</td>\n",
       "      <td>-0.024891</td>\n",
       "      <td>0.352446</td>\n",
       "      <td>0.391405</td>\n",
       "      <td>3.770686</td>\n",
       "      <td>2.770</td>\n",
       "      <td>0.531</td>\n",
       "      <td>1.241255</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>0.000097</td>\n",
       "      <td>1.241134</td>\n",
       "      <td>2.477413</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>247 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Year  inflation  real gdp growth  revtogdp  spendingtogdp  stockindex  \\\n",
       "0    1700        NaN              NaN  0.051348       0.024282   -0.176614   \n",
       "1    1701  -0.049716         0.085921  0.047680       0.031056   -0.274218   \n",
       "2    1702  -0.001810        -0.026129  0.061131       0.047718   -0.212283   \n",
       "3    1703  -0.009189        -0.066233  0.072159       0.057088   -0.062131   \n",
       "4    1704   0.017989         0.174375  0.057906       0.049762   -0.062131   \n",
       "..    ...        ...              ...       ...            ...         ...   \n",
       "242  1942   0.073617         0.017772  0.294740       0.559769    3.570698   \n",
       "243  1943   0.045327         0.017036  0.313066       0.555163    3.633418   \n",
       "244  1944   0.053630        -0.045000  0.331536       0.570380    3.688273   \n",
       "245  1945   0.019649        -0.046810  0.349559       0.543722    3.733123   \n",
       "246  1946   0.031011        -0.024891  0.352446       0.391405    3.770686   \n",
       "\n",
       "     rate_10year  short_r  debttogdplevel        dy  coin_bullion/debt  \\\n",
       "0          6.349      NaN             NaN  0.064618                NaN   \n",
       "1          8.264      NaN             NaN  0.060818                NaN   \n",
       "2          6.557      NaN             NaN  0.064613                NaN   \n",
       "3          5.714      NaN             NaN  0.061515                NaN   \n",
       "4          5.917      NaN             NaN  0.055999                NaN   \n",
       "..           ...      ...             ...       ...                ...   \n",
       "242        2.960    1.031        0.855553  0.044000           0.000074   \n",
       "243        2.980    1.031        0.892668  0.041000           0.000125   \n",
       "244        2.980    1.031        0.980120  0.038000           0.000116   \n",
       "245        2.960    0.531        1.155510  0.038000           0.000171   \n",
       "246        2.770    0.531        1.241255  0.035000           0.000097   \n",
       "\n",
       "     debttogdp-bullion  debttogdp_mil  \n",
       "0                  NaN            NaN  \n",
       "1                  NaN            NaN  \n",
       "2                  NaN            NaN  \n",
       "3                  NaN            NaN  \n",
       "4                  NaN            NaN  \n",
       "..                 ...            ...  \n",
       "242           0.855489       1.555954  \n",
       "243           0.892556       1.748170  \n",
       "244           0.980007       1.995914  \n",
       "245           1.155313       2.354168  \n",
       "246           1.241134       2.477413  \n",
       "\n",
       "[247 rows x 13 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UK1=pd.read_excel('UK_source_pre1946.xlsx',sheet_name='Full_Data_for_VAR')\n",
    "UK1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>inflation</th>\n",
       "      <th>real gdp growth</th>\n",
       "      <th>nominal gdp growth</th>\n",
       "      <th>revenue</th>\n",
       "      <th>spending</th>\n",
       "      <th>revtogdp</th>\n",
       "      <th>spendingtogdp</th>\n",
       "      <th>surplustogdp</th>\n",
       "      <th>stockindex</th>\n",
       "      <th>rate_10year</th>\n",
       "      <th>short_r</th>\n",
       "      <th>yspread</th>\n",
       "      <th>debttogdplevel</th>\n",
       "      <th>dy</th>\n",
       "      <th>debttogdp_mil</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1946</td>\n",
       "      <td>0.002677</td>\n",
       "      <td>-0.024891</td>\n",
       "      <td>0.006120</td>\n",
       "      <td>3438.55</td>\n",
       "      <td>3818.65</td>\n",
       "      <td>0.352446</td>\n",
       "      <td>0.391405</td>\n",
       "      <td>-0.038960</td>\n",
       "      <td>3.770686</td>\n",
       "      <td>2.770</td>\n",
       "      <td>0.531000</td>\n",
       "      <td>2.239000</td>\n",
       "      <td>1.241255</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>2.477413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1947</td>\n",
       "      <td>0.026387</td>\n",
       "      <td>-0.012843</td>\n",
       "      <td>0.077652</td>\n",
       "      <td>3595.75</td>\n",
       "      <td>3036.50</td>\n",
       "      <td>0.341022</td>\n",
       "      <td>0.287983</td>\n",
       "      <td>0.053039</td>\n",
       "      <td>3.715424</td>\n",
       "      <td>3.050</td>\n",
       "      <td>0.531000</td>\n",
       "      <td>2.519000</td>\n",
       "      <td>1.215650</td>\n",
       "      <td>0.043000</td>\n",
       "      <td>2.498459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1948</td>\n",
       "      <td>0.053245</td>\n",
       "      <td>0.031648</td>\n",
       "      <td>0.093806</td>\n",
       "      <td>4115.00</td>\n",
       "      <td>3036.25</td>\n",
       "      <td>0.355323</td>\n",
       "      <td>0.262175</td>\n",
       "      <td>0.093148</td>\n",
       "      <td>3.702352</td>\n",
       "      <td>3.140</td>\n",
       "      <td>0.563000</td>\n",
       "      <td>2.577000</td>\n",
       "      <td>1.064573</td>\n",
       "      <td>0.043000</td>\n",
       "      <td>2.219281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1949</td>\n",
       "      <td>0.048202</td>\n",
       "      <td>0.033251</td>\n",
       "      <td>0.063723</td>\n",
       "      <td>4498.50</td>\n",
       "      <td>3214.50</td>\n",
       "      <td>0.364458</td>\n",
       "      <td>0.260431</td>\n",
       "      <td>0.104027</td>\n",
       "      <td>3.662934</td>\n",
       "      <td>3.600</td>\n",
       "      <td>0.688000</td>\n",
       "      <td>2.912000</td>\n",
       "      <td>1.359927</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>2.067706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1950</td>\n",
       "      <td>0.030130</td>\n",
       "      <td>0.032576</td>\n",
       "      <td>0.045687</td>\n",
       "      <td>4661.75</td>\n",
       "      <td>3376.50</td>\n",
       "      <td>0.360817</td>\n",
       "      <td>0.261339</td>\n",
       "      <td>0.099478</td>\n",
       "      <td>3.701891</td>\n",
       "      <td>3.540</td>\n",
       "      <td>0.688000</td>\n",
       "      <td>2.852000</td>\n",
       "      <td>1.025782</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>1.944232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>2016</td>\n",
       "      <td>0.017769</td>\n",
       "      <td>0.017899</td>\n",
       "      <td>0.035112</td>\n",
       "      <td>666423.75</td>\n",
       "      <td>665478.50</td>\n",
       "      <td>0.343582</td>\n",
       "      <td>0.343094</td>\n",
       "      <td>0.000487</td>\n",
       "      <td>8.237293</td>\n",
       "      <td>1.245</td>\n",
       "      <td>0.332500</td>\n",
       "      <td>0.912500</td>\n",
       "      <td>1.090885</td>\n",
       "      <td>0.036810</td>\n",
       "      <td>1.067650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>2017</td>\n",
       "      <td>0.027029</td>\n",
       "      <td>0.017374</td>\n",
       "      <td>0.036448</td>\n",
       "      <td>738798.00</td>\n",
       "      <td>725445.00</td>\n",
       "      <td>0.357122</td>\n",
       "      <td>0.350667</td>\n",
       "      <td>0.006455</td>\n",
       "      <td>8.303350</td>\n",
       "      <td>1.205</td>\n",
       "      <td>0.192525</td>\n",
       "      <td>1.012475</td>\n",
       "      <td>1.082167</td>\n",
       "      <td>0.035543</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>2018</td>\n",
       "      <td>0.019803</td>\n",
       "      <td>0.013085</td>\n",
       "      <td>0.034695</td>\n",
       "      <td>763614.00</td>\n",
       "      <td>752398.00</td>\n",
       "      <td>0.356530</td>\n",
       "      <td>0.351294</td>\n",
       "      <td>0.005237</td>\n",
       "      <td>8.305728</td>\n",
       "      <td>1.271</td>\n",
       "      <td>0.556912</td>\n",
       "      <td>0.714088</td>\n",
       "      <td>1.073829</td>\n",
       "      <td>0.038181</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>2019</td>\n",
       "      <td>0.012987</td>\n",
       "      <td>0.017840</td>\n",
       "      <td>0.034867</td>\n",
       "      <td>788933.00</td>\n",
       "      <td>784249.00</td>\n",
       "      <td>0.355730</td>\n",
       "      <td>0.353618</td>\n",
       "      <td>0.002112</td>\n",
       "      <td>8.292741</td>\n",
       "      <td>0.825</td>\n",
       "      <td>0.644565</td>\n",
       "      <td>0.180435</td>\n",
       "      <td>1.065695</td>\n",
       "      <td>0.042244</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>2020</td>\n",
       "      <td>0.008261</td>\n",
       "      <td>-0.103398</td>\n",
       "      <td>-0.048856</td>\n",
       "      <td>755281.00</td>\n",
       "      <td>978484.00</td>\n",
       "      <td>0.357608</td>\n",
       "      <td>0.463289</td>\n",
       "      <td>-0.105681</td>\n",
       "      <td>8.181780</td>\n",
       "      <td>0.196</td>\n",
       "      <td>0.124864</td>\n",
       "      <td>0.071136</td>\n",
       "      <td>1.304101</td>\n",
       "      <td>0.047895</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>75 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year  inflation  real gdp growth  nominal gdp growth    revenue  \\\n",
       "0   1946   0.002677        -0.024891            0.006120    3438.55   \n",
       "1   1947   0.026387        -0.012843            0.077652    3595.75   \n",
       "2   1948   0.053245         0.031648            0.093806    4115.00   \n",
       "3   1949   0.048202         0.033251            0.063723    4498.50   \n",
       "4   1950   0.030130         0.032576            0.045687    4661.75   \n",
       "..   ...        ...              ...                 ...        ...   \n",
       "70  2016   0.017769         0.017899            0.035112  666423.75   \n",
       "71  2017   0.027029         0.017374            0.036448  738798.00   \n",
       "72  2018   0.019803         0.013085            0.034695  763614.00   \n",
       "73  2019   0.012987         0.017840            0.034867  788933.00   \n",
       "74  2020   0.008261        -0.103398           -0.048856  755281.00   \n",
       "\n",
       "     spending  revtogdp  spendingtogdp  surplustogdp  stockindex  rate_10year  \\\n",
       "0     3818.65  0.352446       0.391405     -0.038960    3.770686        2.770   \n",
       "1     3036.50  0.341022       0.287983      0.053039    3.715424        3.050   \n",
       "2     3036.25  0.355323       0.262175      0.093148    3.702352        3.140   \n",
       "3     3214.50  0.364458       0.260431      0.104027    3.662934        3.600   \n",
       "4     3376.50  0.360817       0.261339      0.099478    3.701891        3.540   \n",
       "..        ...       ...            ...           ...         ...          ...   \n",
       "70  665478.50  0.343582       0.343094      0.000487    8.237293        1.245   \n",
       "71  725445.00  0.357122       0.350667      0.006455    8.303350        1.205   \n",
       "72  752398.00  0.356530       0.351294      0.005237    8.305728        1.271   \n",
       "73  784249.00  0.355730       0.353618      0.002112    8.292741        0.825   \n",
       "74  978484.00  0.357608       0.463289     -0.105681    8.181780        0.196   \n",
       "\n",
       "     short_r   yspread  debttogdplevel        dy  debttogdp_mil  \n",
       "0   0.531000  2.239000        1.241255  0.035000       2.477413  \n",
       "1   0.531000  2.519000        1.215650  0.043000       2.498459  \n",
       "2   0.563000  2.577000        1.064573  0.043000       2.219281  \n",
       "3   0.688000  2.912000        1.359927  0.050000       2.067706  \n",
       "4   0.688000  2.852000        1.025782  0.050000       1.944232  \n",
       "..       ...       ...             ...       ...            ...  \n",
       "70  0.332500  0.912500        1.090885  0.036810       1.067650  \n",
       "71  0.192525  1.012475        1.082167  0.035543            NaN  \n",
       "72  0.556912  0.714088        1.073829  0.038181            NaN  \n",
       "73  0.644565  0.180435        1.065695  0.042244            NaN  \n",
       "74  0.124864  0.071136        1.304101  0.047895            NaN  \n",
       "\n",
       "[75 rows x 16 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UK2=pd.read_excel('UK_source_post1946.xlsx',sheet_name='final_data')\n",
    "UK2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>inflation</th>\n",
       "      <th>real gdp growth</th>\n",
       "      <th>revtogdp</th>\n",
       "      <th>spendingtogdp</th>\n",
       "      <th>stockindex</th>\n",
       "      <th>rate_10year</th>\n",
       "      <th>short_r</th>\n",
       "      <th>debttogdplevel</th>\n",
       "      <th>dy</th>\n",
       "      <th>coin_bullion/debt</th>\n",
       "      <th>debttogdp-bullion</th>\n",
       "      <th>debttogdp_mil</th>\n",
       "      <th>nominal gdp growth</th>\n",
       "      <th>revenue</th>\n",
       "      <th>spending</th>\n",
       "      <th>surplustogdp</th>\n",
       "      <th>yspread</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1700</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.051348</td>\n",
       "      <td>0.024282</td>\n",
       "      <td>-0.176614</td>\n",
       "      <td>6.349</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.064618</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1701</td>\n",
       "      <td>-0.049716</td>\n",
       "      <td>0.085921</td>\n",
       "      <td>0.047680</td>\n",
       "      <td>0.031056</td>\n",
       "      <td>-0.274218</td>\n",
       "      <td>8.264</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.060818</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1702</td>\n",
       "      <td>-0.001810</td>\n",
       "      <td>-0.026129</td>\n",
       "      <td>0.061131</td>\n",
       "      <td>0.047718</td>\n",
       "      <td>-0.212283</td>\n",
       "      <td>6.557</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.064613</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1703</td>\n",
       "      <td>-0.009189</td>\n",
       "      <td>-0.066233</td>\n",
       "      <td>0.072159</td>\n",
       "      <td>0.057088</td>\n",
       "      <td>-0.062131</td>\n",
       "      <td>5.714</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.061515</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1704</td>\n",
       "      <td>0.017989</td>\n",
       "      <td>0.174375</td>\n",
       "      <td>0.057906</td>\n",
       "      <td>0.049762</td>\n",
       "      <td>-0.062131</td>\n",
       "      <td>5.917</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.055999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>2016</td>\n",
       "      <td>0.017769</td>\n",
       "      <td>0.017899</td>\n",
       "      <td>0.343582</td>\n",
       "      <td>0.343094</td>\n",
       "      <td>8.237293</td>\n",
       "      <td>1.245</td>\n",
       "      <td>0.332500</td>\n",
       "      <td>1.090885</td>\n",
       "      <td>0.036810</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.06765</td>\n",
       "      <td>0.035112</td>\n",
       "      <td>666423.75</td>\n",
       "      <td>665478.5</td>\n",
       "      <td>0.000487</td>\n",
       "      <td>0.912500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>2017</td>\n",
       "      <td>0.027029</td>\n",
       "      <td>0.017374</td>\n",
       "      <td>0.357122</td>\n",
       "      <td>0.350667</td>\n",
       "      <td>8.303350</td>\n",
       "      <td>1.205</td>\n",
       "      <td>0.192525</td>\n",
       "      <td>1.082167</td>\n",
       "      <td>0.035543</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.036448</td>\n",
       "      <td>738798.00</td>\n",
       "      <td>725445.0</td>\n",
       "      <td>0.006455</td>\n",
       "      <td>1.012475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>2018</td>\n",
       "      <td>0.019803</td>\n",
       "      <td>0.013085</td>\n",
       "      <td>0.356530</td>\n",
       "      <td>0.351294</td>\n",
       "      <td>8.305728</td>\n",
       "      <td>1.271</td>\n",
       "      <td>0.556912</td>\n",
       "      <td>1.073829</td>\n",
       "      <td>0.038181</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.034695</td>\n",
       "      <td>763614.00</td>\n",
       "      <td>752398.0</td>\n",
       "      <td>0.005237</td>\n",
       "      <td>0.714088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>2019</td>\n",
       "      <td>0.012987</td>\n",
       "      <td>0.017840</td>\n",
       "      <td>0.355730</td>\n",
       "      <td>0.353618</td>\n",
       "      <td>8.292741</td>\n",
       "      <td>0.825</td>\n",
       "      <td>0.644565</td>\n",
       "      <td>1.065695</td>\n",
       "      <td>0.042244</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.034867</td>\n",
       "      <td>788933.00</td>\n",
       "      <td>784249.0</td>\n",
       "      <td>0.002112</td>\n",
       "      <td>0.180435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>2020</td>\n",
       "      <td>0.008261</td>\n",
       "      <td>-0.103398</td>\n",
       "      <td>0.357608</td>\n",
       "      <td>0.463289</td>\n",
       "      <td>8.181780</td>\n",
       "      <td>0.196</td>\n",
       "      <td>0.124864</td>\n",
       "      <td>1.304101</td>\n",
       "      <td>0.047895</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.048856</td>\n",
       "      <td>755281.00</td>\n",
       "      <td>978484.0</td>\n",
       "      <td>-0.105681</td>\n",
       "      <td>0.071136</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>321 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year  inflation  real gdp growth  revtogdp  spendingtogdp  stockindex  \\\n",
       "0   1700        NaN              NaN  0.051348       0.024282   -0.176614   \n",
       "1   1701  -0.049716         0.085921  0.047680       0.031056   -0.274218   \n",
       "2   1702  -0.001810        -0.026129  0.061131       0.047718   -0.212283   \n",
       "3   1703  -0.009189        -0.066233  0.072159       0.057088   -0.062131   \n",
       "4   1704   0.017989         0.174375  0.057906       0.049762   -0.062131   \n",
       "..   ...        ...              ...       ...            ...         ...   \n",
       "70  2016   0.017769         0.017899  0.343582       0.343094    8.237293   \n",
       "71  2017   0.027029         0.017374  0.357122       0.350667    8.303350   \n",
       "72  2018   0.019803         0.013085  0.356530       0.351294    8.305728   \n",
       "73  2019   0.012987         0.017840  0.355730       0.353618    8.292741   \n",
       "74  2020   0.008261        -0.103398  0.357608       0.463289    8.181780   \n",
       "\n",
       "    rate_10year   short_r  debttogdplevel        dy  coin_bullion/debt  \\\n",
       "0         6.349       NaN             NaN  0.064618                NaN   \n",
       "1         8.264       NaN             NaN  0.060818                NaN   \n",
       "2         6.557       NaN             NaN  0.064613                NaN   \n",
       "3         5.714       NaN             NaN  0.061515                NaN   \n",
       "4         5.917       NaN             NaN  0.055999                NaN   \n",
       "..          ...       ...             ...       ...                ...   \n",
       "70        1.245  0.332500        1.090885  0.036810                NaN   \n",
       "71        1.205  0.192525        1.082167  0.035543                NaN   \n",
       "72        1.271  0.556912        1.073829  0.038181                NaN   \n",
       "73        0.825  0.644565        1.065695  0.042244                NaN   \n",
       "74        0.196  0.124864        1.304101  0.047895                NaN   \n",
       "\n",
       "    debttogdp-bullion  debttogdp_mil  nominal gdp growth    revenue  spending  \\\n",
       "0                 NaN            NaN                 NaN        NaN       NaN   \n",
       "1                 NaN            NaN                 NaN        NaN       NaN   \n",
       "2                 NaN            NaN                 NaN        NaN       NaN   \n",
       "3                 NaN            NaN                 NaN        NaN       NaN   \n",
       "4                 NaN            NaN                 NaN        NaN       NaN   \n",
       "..                ...            ...                 ...        ...       ...   \n",
       "70                NaN        1.06765            0.035112  666423.75  665478.5   \n",
       "71                NaN            NaN            0.036448  738798.00  725445.0   \n",
       "72                NaN            NaN            0.034695  763614.00  752398.0   \n",
       "73                NaN            NaN            0.034867  788933.00  784249.0   \n",
       "74                NaN            NaN           -0.048856  755281.00  978484.0   \n",
       "\n",
       "    surplustogdp   yspread  \n",
       "0            NaN       NaN  \n",
       "1            NaN       NaN  \n",
       "2            NaN       NaN  \n",
       "3            NaN       NaN  \n",
       "4            NaN       NaN  \n",
       "..           ...       ...  \n",
       "70      0.000487  0.912500  \n",
       "71      0.006455  1.012475  \n",
       "72      0.005237  0.714088  \n",
       "73      0.002112  0.180435  \n",
       "74     -0.105681  0.071136  \n",
       "\n",
       "[321 rows x 18 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UK_final=pd.concat([UK1.iloc[:-1],UK2])\n",
    "UK_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ADMINI~1\\AppData\\Local\\Temp/ipykernel_28940/1274759450.py:1: UserWarning: Pandas requires version '3.0.3' or newer of 'xlsxwriter' (version '3.0.1' currently installed).\n",
      "  UK_final.to_excel('UK_full.xlsx',index=False)\n"
     ]
    }
   ],
   "source": [
    "UK_final.to_excel('UK_full.xlsx',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
